package com.bot.adms.dao.impl;

import com.bot.adms.dao.ICheckRecordInfoDao;
import com.bot.adms.model.entity.CheckRecordInfo;
import com.bot.adms.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 审核记录信息表持久层接口实现
 * 
 * @author FZhao
 * @version 1.0.1
 */
public class CheckRecordInfoDaoImpl implements ICheckRecordInfoDao {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    /**
     * 添加审核记录
     *
     * @param checkRecordInfo 审核记录信息，包含：审核人名称,申请人名称等等
     * @return Integer 添加成功的数量
     */
    @Override
    public Integer insertCheckRecordInfo(CheckRecordInfo checkRecordInfo) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sql = "INSERT INTO `bo_adms`.`check_record_info` (`check_man_name`, `apply_man_name`, `check_opinion`, `check_time`, `apply_id`, `apply_title`) VALUES ('"
                    + checkRecordInfo.getCheckManName() + "', '" + checkRecordInfo.getApplyManName() + "', '"
                    + checkRecordInfo.getCheckOpinion() + "', '" + dateFormat.format(checkRecordInfo.getCheckTime()) + "', " + checkRecordInfo.getApplyId() + ", '" + checkRecordInfo.getApplyTitle() + "');";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 修改审核记录
     *
     * @param checkRecordInfo 审核记录信息，包含：审核人名称,申请人名称等等
     * @return Integer 修改成功的数量
     */
    @Override
    public Integer updateCheckRecordInfo(CheckRecordInfo checkRecordInfo) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sql = "UPDATE `bo_adms`.`check_record_info` SET `check_man_name` = '" + checkRecordInfo.getCheckManName() + "', `apply_man_name` = '" + checkRecordInfo.getApplyManName() + "', `check_opinion` = '" + checkRecordInfo.getCheckOpinion() + "', `check_time` = '" + dateFormat.format(checkRecordInfo.getCheckTime()) + "', `apply_title` = '" + checkRecordInfo.getApplyTitle() + "' WHERE `id` = " + checkRecordInfo.getId() + ";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 删除审核记录
     *
     * @param id 审核记录id
     * @return Integer 删除成功的数量
     */
    @Override
    public Integer deleteCheckRecordInfo(Long id) {
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "DELETE FROM `bo_adms`.`check_record_info` WHERE `id` = " + id + ";";
            //4.执行SQL语句
            int count = stmt.executeUpdate(sql);
            //5.返回执行条数
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
    }

    /**
     * 查找（获取）审核记录信息
     *
     * @param id 审核记录id
     * @return CheckRecordInfo 审核记录信息，包含：审核人名称,申请人名称等等
     */
    @Override
    public CheckRecordInfo getCheckRecordInfoById(Long id) {
        CheckRecordInfo checkRecordInfo = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`check_record_info` WHERE `id` = " + id + ";";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                checkRecordInfo = new CheckRecordInfo();
                checkRecordInfo.setId(rs.getLong("id"));
                checkRecordInfo.setCheckManName(rs.getString("check_man_name"));
                checkRecordInfo.setApplyManName(rs.getString("apply_man_name"));
                checkRecordInfo.setCheckOpinion(rs.getString("check_opinion"));
                checkRecordInfo.setCheckTime(rs.getTimestamp("check_time"));
                checkRecordInfo.setApplyId(rs.getLong("apply_id"));
                checkRecordInfo.setApplyTitle(rs.getString("apply_title"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return checkRecordInfo;
    }

    /**
     * 查询审核记录信息
     *
     * @param checkRecordInfo 审核记录信息，包含：审核人名称,申请人名称等等
     * @return List<CheckRecordInfo> 审核记录信息集合，包含：审核人名称,申请人名称等等
     */
    @Override
    public List<CheckRecordInfo> searchCheckRecordInfo(CheckRecordInfo checkRecordInfo) {
        List<CheckRecordInfo> checkRecordInfoList = new ArrayList<>();
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            //3.SQL语句
            String sql = "SELECT * FROM `bo_adms`.`check_record_info` WHERE 1=1";
            if (checkRecordInfo.getCheckManName() != null && !checkRecordInfo.getCheckManName().isEmpty()) {
                sql += " AND check_man_name LIKE '%" + checkRecordInfo.getCheckManName() + "%'";
            }
            if (checkRecordInfo.getApplyManName() != null && !checkRecordInfo.getApplyManName().isEmpty()) {
                sql += " AND apply_man_name LIKE '%" + checkRecordInfo.getApplyManName() + "%'";
            }
            if (checkRecordInfo.getApplyId() != null) {
                sql += " AND apply_id = " + checkRecordInfo.getApplyId();
            }
            if (checkRecordInfo.getApplyTitle() != null && !checkRecordInfo.getApplyTitle().isEmpty()) {
                sql += " AND apply_title LIKE '%" + checkRecordInfo.getApplyTitle() + "%'";
            }
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                CheckRecordInfo info = new CheckRecordInfo();
                info.setId(rs.getLong("id"));
                info.setApplyTitle(rs.getString("apply_title"));
                info.setCheckManName(rs.getString("check_man_name"));
                info.setApplyManName(rs.getString("apply_man_name"));
                info.setCheckOpinion(rs.getString("check_opinion"));
                info.setCheckTime(rs.getTimestamp("check_time"));
                info.setApplyId(rs.getLong("apply_id"));
                checkRecordInfoList.add(info);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return checkRecordInfoList;
    }
}
